/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package hk.ces.oxfam.patch.db;

import hk.ces.oxfam.patch.connection.ConnectionUtil;
import hk.ces.oxfam.patch.db.result.BaseSQLResult;
import hk.ces.oxfam.patch.db.result.ExcuteResult;
import hk.ces.oxfam.patch.db.result.ExcuteStatus;
import hk.ces.oxfam.patch.db.result.SQLResult;
import hk.ces.oxfam.patch.db.sql.PreparedSQL;
import hk.ces.oxfam.patch.obj.Bean;
import hk.ces.oxfam.patch.obj.BeanPacker;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author ReutyCheng
 */
public class SQLUtil
{
  private SQLUtil()
  {
  }

  public static SQLResult getDatabaseMetaData(Connection connection) throws SQLException
  {
    DatabaseMetaData databaseMetaData = connection.getMetaData();
    ResultSet resultSet = databaseMetaData.getTables(null, "DB2INST1", "%", null);

    try
    {
      BaseSQLResult baseResult = new BaseSQLResult(new PreparedSQL("", new Object[]
      {
      }));
      baseResult.setColumnNames(getColumnNames(resultSet));
      baseResult.setRows(getResults(resultSet));

      return baseResult;
    }
    finally
    {
      ConnectionUtil.closeResource(null, resultSet);
    }
  }

  public static ExcuteResult execute(Connection connection, PreparedSQL preparedSQL) throws SQLException
  {
    PreparedStatement statement = getPrepareStatement(connection, preparedSQL.getSql(), preparedSQL.getParams());

    try
    {
      int resultCount = statement.executeUpdate();
      ExcuteStatus excuteStatus = ConnectionUtil.excute(connection);
      return new ExcuteResult(resultCount, excuteStatus);
    }
    finally
    {
      ConnectionUtil.closeResource(statement, null);
    }
  }

  public static <T> T single(Connection connection, PreparedSQL preparedSQL, Class<T> clazz) throws SQLException
  {
    PreparedStatement statement = getPrepareStatement(connection, preparedSQL.getSql(), preparedSQL.getParams());
    ResultSet resultSet = statement.executeQuery();

    try
    {
      while (resultSet.next())
      {
        return (T) resultSet.getObject(1);
      }

      return null;
    }
    finally
    {
      ConnectionUtil.closeResource(statement, resultSet);
    }
  }

  public static <T extends Bean> T firstRow(Connection connection, PreparedSQL preparedSQL, BeanPacker<T> beanPack) throws SQLException
  {
    PreparedStatement statement = getPrepareStatement(connection, preparedSQL.getSql(), preparedSQL.getParams());
    ResultSet resultSet = statement.executeQuery();

    try
    {
      List<Object[]> resultList = getResults(resultSet);
      if (resultList.isEmpty())
      {
        return null;
      }
      else
      {
        Object[] innerResult = resultList.iterator().next();
        return beanPack.pack(innerResult);
      }
    }
    finally
    {
      ConnectionUtil.closeResource(statement, resultSet);
    }
  }

  public static <T> List<T> certainColumn(Connection connection, PreparedSQL preparedSQL, Class<T> clazz) throws SQLException
  {
    PreparedStatement statement = getPrepareStatement(connection, preparedSQL.getSql(), preparedSQL.getParams());
    ResultSet resultSet = statement.executeQuery();

    try
    {
      List<T> list = new ArrayList<T>();
      while (resultSet.next())
      {
        list.add((T) resultSet.getObject(1));
      }

      return list;
    }
    finally
    {
      ConnectionUtil.closeResource(statement, resultSet);
    }
  }

  public static SQLResult query(Connection connection, PreparedSQL preparedSQL) throws SQLException
  {
//    Logger.getLogger(SQLUtil.class).debug(preparedSQL.getSql());
    PreparedStatement statement = getPrepareStatement(connection, preparedSQL.getSql(), preparedSQL.getParams());
    ResultSet resultSet = statement.executeQuery();

    try
    {
      BaseSQLResult baseResult = new BaseSQLResult(preparedSQL);
      baseResult.setColumnNames(getColumnNames(resultSet));
      baseResult.setRows(getResults(resultSet));

      return baseResult;
    }
    finally
    {
      ConnectionUtil.closeResource(statement, resultSet);
    }
  }

  public static PreparedStatement getPrepareStatement(Connection connection, String sql, Object[] objects) throws SQLException
  {
    PreparedStatement statement = connection.prepareStatement(sql);
    return setParameters(statement, objects);
  }

  private static PreparedStatement setParameters(PreparedStatement statement, Object[] objects) throws SQLException
  {
    for (int index = 1; index < objects.length + 1; index++)
    {
      statement = setParameter(statement, index, objects[index - 1]);
    }

    return statement;
  }

  private static PreparedStatement setParameter(PreparedStatement statement, int paramIndex, Object object) throws SQLException
  {
    statement.setObject(paramIndex, object);
    return statement;
  }

  private static String[] getColumnNames(ResultSet resultSet) throws SQLException
  {
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();

    String[] columnNames = new String[columnCount];
    for (int column = 0; column < columnCount; column++)
    {
      columnNames[column] = metaData.getColumnName(column + 1);
    }

    return columnNames;
  }

  private static List<Object[]> getResults(ResultSet resultSet) throws SQLException
  {
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();

    List<Object[]> results = new ArrayList<Object[]>(100);
    while (resultSet.next())
    {
      Object[] innerResult = new Object[columnCount];
      for (int column = 0; column < columnCount; column++)
      {
        innerResult[column] = resultSet.getObject(column + 1);
      }
      results.add(innerResult);
    }

    return results;
  }
}
